<?php

namespace classes;
class Excel
{
    public static function writer($title, $data, $fileName = '', $count = 7)
    {
        $writer = new \XLSXWriter();
        //文件名
        $filename = iconv("utf-8", "gb2312", $fileName) . '.xlsx'; // 重命名表
        //设置 header，用于浏览器下载
        header('Content-disposition: attachment; filename="' . \XLSXWriter::sanitize_filename($filename) . '"');
        header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header('Content-Transfer-Encoding: binary');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');

        //每列的标题头

        //工作簿名称
        $sheet1 = 'sheet1';

        //对每列指定数据类型，对应单元格的数据类型
        $col_style = [];
        foreach ($title as $key => $item) {
//            $col_style[] = $key == 5 ? 'price' : 'string';
            $col_style[] = 'string';
        }
        //设置列格式，suppress_row: 去掉会多出一行数据；widths: 指定每列宽度
        $writer->writeSheetHeader($sheet1, $col_style, ['suppress_row' => true, 'widths' => [20, 20, 20, 20, 20, 20, 20, 20]]);

        //写入第二行的数据，顺便指定样式
        $writer->writeSheetRow($sheet1, [$fileName],
            ['height' => 32, 'font-size' => 16, 'font-style' => 'bold', 'halign' => 'center', 'valign' => 'center']);

        /*设置标题头，指定样式*/
        $styles1 = array('font' => '宋体', 'font-size' => 14, 'font-style' => 'bold', 'fill' => '#eee',
            'halign' => 'center', 'border' => 'left,right,top,bottom');

        $writer->writeSheetRow($sheet1, $title, $styles1);
        $styles2 = ['height' => 16];
        foreach ($data as $row) {
            $writer->writeSheetRow($sheet1, $row, $styles2);
        }

        //合并单元格，第一行的大标题需要合并单元格
        $writer->markMergedCell($sheet1, $start_row = 0, $start_col = 0, $end_row = 0, $end_col = $count);
        //输出文档
        $writer->writeToStdOut();
        return true;
    }


    //excel文件导入
    public static function importExcel($file){
        //include(__DIR__.'/../../../vendor/PHPExcel/Classes/PHPExcel.php');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
        $cacheSettings = array('memoryCacheSize' => '16MB');
        \PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);//文件缓存
        //当前空间不用\，非当前空间要加\
        $PHPExcel = new \PHPExcel();//创建一个excel对象
        $PHPReader = new \PHPExcel_Reader_Excel2007(); //建立reader对象，excel—2007以后格式
        if (!$PHPReader->canRead($file)) {
            $PHPReader = new \PHPExcel_Reader_Excel5();//建立reader对象，excel—2007以前格式
            if (!$PHPReader->canRead($file)) {
                $msg = '不是excel格式文件';
                return $msg;
            }
        }
        $PHPExcel = $PHPReader->load($file); //加载excel对象
        $sheet = $PHPExcel->getSheet(0); //获取指定的sheet表
        $rows = $sheet->getHighestRow();//行数
        $cols = $sheet->getHighestColumn();//列数

        $data = array();
        for ($i = 1; $i <= $rows; $i++){ //行数是以第1行开始
            $count = 0;
            for ($j = 'A'; $j <= $cols; $j++) { //列数是以A列开始
                $value = $sheet->getCell($j . $i)->getValue();
                if ($value) {
                    $data[$i - 1][$count] = (string)$sheet->getCell($j . $i)->getValue();
                    $count += 1;
                }
            }
        }
        return $data;
    }
}